﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ExpenseTracker.Platform
{
    public class SqlServerDataHandler
    {
        private static DataTable expenseTypeDataTable = null;

        private static string sqlServerConnectionString =
            ConfigurationManager.ConnectionStrings["SQL_Server_Connection_String"].ConnectionString;

        #region methods -------------------------

        public DataTable GetData(int month_, int year_)
        {
            string query = String.Format("SELECT e.Id, e.Amount,  et.Type, e.Date, e.Comment" +
                        " FROM expense AS e INNER JOIN  ExpenseType AS et" +
                        " ON et.Id=e.ExpenseType" +
                        " where DATEPART(month, Date) = {0} AND DATEPART(year, Date) = {1}", ++month_, year_);
            DataTable dt = new DataTable();
            using (SqlConnection newConnection = new SqlConnection(sqlServerConnectionString))
            {
                newConnection.Open();
                using (SqlDataAdapter newAdapter = new SqlDataAdapter(query, newConnection))
                {
                    newAdapter.Fill(dt);
                }
            }
            return dt;
        }

        public static DataTable GetExpenseTypes()
        {
            if (expenseTypeDataTable == null)
            {
                string query = "SELECT * FROM ExpenseType";
                expenseTypeDataTable = new DataTable();
                using (SqlConnection newConnection = new SqlConnection(sqlServerConnectionString))
                {
                    newConnection.Open();
                    using (SqlDataAdapter newAdapter = new SqlDataAdapter(query, newConnection))
                    {
                        newAdapter.Fill(expenseTypeDataTable);
                    }
                }
            }
            return expenseTypeDataTable;
        }

        public static int GetExpenseId(string expenseType)
        {
            int expenseId = 0;
            if (expenseTypeDataTable == null)
            {
                GetExpenseTypes();
            }
            foreach (DataRow row in expenseTypeDataTable.Rows)
            {
                if (string.Equals((string)row["Type"], expenseType, StringComparison.OrdinalIgnoreCase))
                {
                    expenseId = (int)row["Id"];
                }
            }
            return expenseId;
        }

        public bool InsertData(List<ExpenseEntry> expenseEntriesToSave_)
        {
            using (SqlConnection newConnection = new SqlConnection(sqlServerConnectionString))
            {
                newConnection.Open();
                SqlCommand command = new SqlCommand("INSERT INTO expense (Amount, ExpenseType, Date, Comment)" +
                    " VALUES (@amount, @expenseType, @date, @comment)", newConnection);
                string tempValue = string.Empty;
                command.Parameters.AddWithValue("@amount", tempValue);
                command.Parameters.AddWithValue("@expenseType", tempValue);
                command.Parameters.AddWithValue("@date", tempValue);
                command.Parameters.AddWithValue("@comment", tempValue);

                int count = 0;
                foreach (ExpenseEntry newExpense in expenseEntriesToSave_)
                {
                    SqlCommand checkIdCommand = new SqlCommand("SELECT COUNT(*) FROM expense WHERE Id=@id", newConnection);
                    checkIdCommand.Parameters.Add("@id", newExpense.Id);
                    if (Convert.ToInt32(checkIdCommand.ExecuteScalar()) >= 1)
                    {
                        Console.WriteLine("Expense entry already exists. Skipping");
                        continue;
                    }
                    command.Parameters["@amount"].Value = newExpense.Amount;
                    command.Parameters["@expenseType"].Value = newExpense.ExpenseType;
                    command.Parameters["@date"].Value = newExpense.Date;  
                    command.Parameters["@comment"].Value = newExpense.Comment;
                    count += command.ExecuteNonQuery();
                }
                Console.WriteLine("number of lines affected " + count);
            }
            return true;
        }

        #endregion -----------------------------
    }
}
